﻿using H2.NET.Common;
using H2.NET.DBUtility;
using H2.NET.Web.BI.Model;
using H2.NET.Web.Workflow.DwzCode;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace H2.NET.Web.BI.Template
{
    public partial class Exec : BasePage
    {
        public String where = "";
        public String widths = "";
        public String ta = "";
        public String tempSql = "";
        public String totalSql = "";
        public int PageSize = 20;
        public int PageNumber = 1;
        public int totalCount = 0;
        public int tid = 0;
        public Dictionary<string, string> searchVal = new Dictionary<string, string>();
        public Dictionary<string, string> searchType = new Dictionary<string, string>();
        public List<bi_Column> searchList = new List<bi_Column>();
        //public int isSum = 0;
        public DataTable result = new DataTable();
        public DataTable result1 = new DataTable();
        protected void Page_Load(object sender, EventArgs e)
        {
            var context = new UltimusMDC3Entities();
            int id = AksRequest.GetQueryInt("id");
            if (id > 0)
            {
                tid = id;
                var template = context.bi_Template.Find(id);
                if (template == null)
                {
                    ErrorMsg("对不起，该模版不存在，请刷新页面！");
                    return;
                }
                string sql = template.sql;
                string tsql = template.sql;
                where = AksRequest.GetQueryString("where") ?? "";
                //isSum = template.bi_TemplateDetail.Where(t => t.sum == true).Count() > 0 ? 1 : 0;
                string val = AksRequest.GetQueryString("vals");
                string type = AksRequest.GetQueryString("types");
                string ps = AksRequest.GetQueryString("pageSize");
                string pn = AksRequest.GetQueryString("PageNumber");
                foreach (var detail in template.bi_TemplateDetail.Where(b=>b.fix==false&&b.filter==false))
                {
                    widths += detail.bi_Column.bi_Table.bi_DB.name + ".dbo." + detail.bi_Column.bi_Table.table_name + "." + detail.bi_Column.column_name + "|" + detail.width + ",";
                }
                foreach (var detail in template.bi_TemplateDetail.Where(b => b.fix == false && b.filter == false))
                {
                    ta += detail.bi_Column.bi_Table.bi_DB.name + ".dbo." + detail.bi_Column.bi_Table.table_name + "." + detail.bi_Column.column_name + "|" + detail.textAlign + ",";
                }
                if (!string.IsNullOrEmpty(ps))
                {
                    PageSize = int.Parse(ps);
                }
                if (!string.IsNullOrEmpty(pn))
                {
                    PageNumber = int.Parse(pn);
                }
                if (!string.IsNullOrEmpty(val))
                {
                    List<string> vals = val.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                    foreach (var v in vals)
                    {
                        searchVal.Add(v.Substring(0, v.IndexOf("=")), v.Substring(v.IndexOf("=") + 1));
                    }
                }
                if (!string.IsNullOrEmpty(type))
                {
                    List<string> types = type.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                    foreach (var t in types)
                    {
                        searchType.Add(t.Substring(0, t.IndexOf("=")), t.Substring(t.IndexOf("=") + 1));
                    }
                }
                tempSql = sql;
                totalSql = tsql;
                if (template.bi_TemplateDetail.Count() > 0)
                {
                    List<int> colIDs = template.bi_TemplateDetail.Where(t => t.filter == true).Select(t => t.column_id).ToList();
                    searchList = context.bi_Column.Where(b => colIDs.Contains(b.id)).ToList();
                }
                if (!string.IsNullOrEmpty(where))
                {
                    sql = sql.Replace(" where", " " + where + " and ").Replace(" nowhere", " " + where);
                    tsql = tsql.Replace(" where", " " + where + " and ").Replace(" nowhere", " " + where);
                }
                else
                {
                    sql = sql.Replace("nowhere", "");
                    tsql = tsql.Replace("nowhere", "");
                }
                string pageFooter = ") tt where tt.Row between " + ((PageNumber - 1) * PageSize + 1) + " and " + (PageNumber * PageSize);
                if (sql.IndexOf("union") >= 0)
                {
                    sql = sql.Replace("union", pageFooter + " union ");
                    result = DbHelperSQL.Query(sql.Substring(0, sql.IndexOf("union"))).Tables[0];
                    result1 = DbHelperSQL.Query(sql.Substring(sql.IndexOf("union") + 5)).Tables[0];
                   // result = AppendDataTable(tempData1, tempData2);
                    tsql = tsql.Substring(0, tsql.IndexOf("union")) + " ) tt";
                }
                else
                {
                    sql = sql + pageFooter;
                    result = DbHelperSQL.Query(sql).Tables[0];
                    tsql += " ) tt";
                }
                DataTable totalResult = DbHelperSQL.Query(tsql).Tables[0];
                if (totalResult != null)
                {
                    totalCount = totalResult.Rows.Count;
                }
            }
            else
            {
                ErrorMsg("对不起，该页无法刷新，请重新查询或检索数据！");
                return;
            }
            
        }
        //public static DataTable AppendDataTable(DataTable hostDt, DataTable clientDt)
        //{
        //    if (hostDt != null && hostDt.Rows.Count > 0)
        //    {
        //        object[] obj = new object[hostDt.Columns.Count];
        //        for (int i = 0; i < clientDt.Rows.Count; i++)
        //        {
        //            clientDt.Rows[i].ItemArray.CopyTo(obj, 0);
        //            hostDt.Rows.Add(obj);
        //        }
        //    }
        //    return hostDt;
        //}
    }
}